package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

/**
 * DBEmployee.java
 * @author Toke Olsen
 * @version 25/10-2012
 *
 */

public class DBInvoice
{
	private Connection con;
	
	//Constructor
	public DBInvoice() 
	{
		con = DbConnection1.getInstance().getDBcon();
	}
	
	//Methods
	public ArrayList<Invoice> getAllInvoices(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}
	
	public Invoice findInvoice(String invNo, boolean retrieveAssociation) 
	{
		String wClause = " invoiceNo = " + invNo + "'";
		return singleWhere(wClause, retrieveAssociation);
	}
	
	public Invoice searchInvoiceNo(String attValue, boolean retriveAssociation)
    {
        String wClause = "invoiceNo like '%" + attValue + "%'";
        System.out.println("SearchInvoice " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
	
	public Invoice searchPaymentDate(String attValue, boolean retriveAssociation)
    {
        String wClause = "Payment Date = '" + attValue + "'";
        System.out.println("SearchPaymentDate " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
	
	
    public int insertInvoice(Invoice inv) throws Exception
    {  //call to get the next invoice number
        int nextNo = GetMax.getMaxId("Select max(invoiceNo) from invoice");
        nextNo = nextNo + 1;
        System.out.println("next no = " +  nextNo);
  
        int rc = -1;
        String query="INSERT INTO invoice(invoiceNo, paymentDate, amount)  VALUES('"+
        	nextNo + "','"  +
		    inv.getPaymentDate()  + "','"  +
            inv.getAmount() + "','" + "')";
                     

   		System.out.println("insert : " + query);
   		try{ // insert new invoice
   			Statement stmt = con.createStatement();
   			stmt.setQueryTimeout(5);
   			rc = stmt.executeUpdate(query);
   			stmt.close();
   		}//end try
   		catch(SQLException ex){
   			System.out.println("Invoice not created");
   			throw new Exception ("Employee is not inserted correct");
   		}
   		return(rc);
    }
    
    
    public int updateInvoice(Invoice inv)
	{
		Invoice invObj  = inv;
		int rc=-1;

		String query="UPDATE invocie SET "+
		 	  "invoiceNo ='"+ invObj.getInvoiceNo()+"', "+
		 	  "PaymentDate ='"+ invObj.getPaymentDate() + "', " +
              "amount ='"+ invObj.getAmount() + "'";
                System.out.println("Update query:" + query);
  		try{ // update invoice
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in invoice db: "+ex);
	  	}
		return(rc);
	}
    
    
    public int delete(String invoiceNo)
	{
       int rc=-1;
	  
       String query="DELETE FROM invoice WHERE invoiceNo = '" + invoiceNo + "'";
       System.out.println(query);
       try{ // delete from employee
    	   Statement stmt = con.createStatement();
    	   stmt.setQueryTimeout(5);
    	   rc = stmt.executeUpdate(query);
    	   stmt.close();
       }//end try	
   	   catch(Exception ex){
   		   System.out.println("Delete exception in invoice db: "+ex);
   	   }
       
       return(rc);
	}
	
    
    private ArrayList<Invoice> miscWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
	    ArrayList<Invoice> list = new ArrayList<Invoice>();	
		
	    String query =  buildQuery(wClause);
  
        try{ // read the invoice from the database
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);
	 	
	
			while( results.next() ){
				Invoice invObj = new Invoice();
		     	invObj = buildInvoice(results);	
		     	list.add(invObj);	
			}//end while
			
			stmt.close();  
			
			/*if(retrieveAssociation)
		    {   //The supervisor and department is to be build as well
				for(Employee empObj : list){
		            String superssn = empObj.getSupervisor().getSsn();
		            Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
		            empObj.setSupervisor(superEmp);
		            System.out.println("Supervisor is selected");
		           // here the department has to be selected as well
		        }
		    }//end if*/   
				
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
        
		return list;
	}
    
    private Invoice singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Invoice invObj = new Invoice();
                
        String query =  buildQuery(wClause);
        System.out.println(query);
        
		try{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 			invObj = buildInvoice(results);
                //association is to be build
                stmt.close();
                
                /*if(retrieveAssociation)
                {   //The supervisor and department is to be build as well
                    String superssn = empObj.getSupervisor().getSsn();
                    Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
                    empObj.setSupervisor(superEmp);
                    System.out.println("Supervisor is seleceted");
                   // here the department has to be selected as well
                }*/
			}
            else{ //no invoice was found
            	invObj = null;
            }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return invObj;
	}
    
    
    
    private String buildQuery(String wClause)
	{
	    String query="SELECT invoiceNo, paymentdate, amount FROM Invoice";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
    
    private Invoice buildInvoice(ResultSet results)
    {   
    	Invoice invObj = new Invoice();
        try{ // the columns from the table invoice  are used
        	invObj.setInvoiceNo(results.getString("invoiceno"));
        	invObj.setPaymentDate(results.getString("paymentdate"));
        	invObj.setAmount(results.getInt("amount"));

        }
       catch(Exception e)
       {
           System.out.println("error in building the invoice object");
       }
       return invObj;
    }
}//end class
